using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;   

namespace Metro.Admin.DataAccess
{
    partial class AdminDataSource
    {
        public List<Customer> GetCustomers()
        {
            List<Customer> customers = null;
            string id = string.Empty;
            string nemotechnic = string.Empty;
            int idCity = 0;
            City customerCity;
            string documentType=string.Empty;
            string state = string.Empty;
            State customerState = State.Active;  
            string customerType = string.Empty;
            CustomerType cusType = CustomerType.Cambiario;
            string document = string.Empty;
            string name = string.Empty;
            string sureName = string.Empty;

            try
            {
                customers = new List<Customer>();
                string sqlCommand = "SELECT [name],[sureName],[idCustomer],[idCity],[nemotechnic],[documentType],[state],[customerType],[document] FROM [Customers] WHERE state='A'";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                       id = dataReader["idCustomer"].ToString();
                       idCity= Convert.ToInt32(dataReader["idCity"]);
                       nemotechnic = dataReader["nemotechnic"].ToString();
                       documentType = dataReader["documentType"].ToString();
                       state = dataReader["state"].ToString();
                       customerType = dataReader["customerType"].ToString();
                       document = dataReader["document"].ToString();
                       name = dataReader["name"].ToString();
                       sureName = dataReader["sureName"].ToString();
                       
                       customerCity = this.GetCity(idCity);
                       customerState = CustomerServices.GetState(state);
                       cusType = CustomerServices.GetCustomerType(customerType);

                       customers.Add(new Customer(name, sureName, id, customerCity.Id, customerCity.Name, nemotechnic, documentType, customerState, cusType, document));
                    }
                }
            }
            catch(Exception ex)
            {
                throw ex;            
            }

            return customers;
        }
        public void UpdateCustomer(Customer customer)
        {
            Guid idCustomer = new Guid(customer.Id);
            try
            {
                string sqlCommand = "UPDATE [Customer] SET [idCity] = @idCity, [nemotechnic] = @nemotechnic,"+
                                    "[state] = @state,[customerType] = @customerType,[document] = @document WHERE idCustomer = @idCustomer";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.AddInParameter(dbCommand, "@idCity", DbType.Int32, int.Parse(customer.CityId));
                metroDb.AddInParameter(dbCommand, "@nemotechnic", DbType.String, customer.TechnicalName);
                metroDb.AddInParameter(dbCommand, "@state", DbType.String, CustomerServices.State2String(customer.State));
                metroDb.AddInParameter(dbCommand, "@customerType", DbType.String, CustomerServices.CustomerType2String(customer.CustomerType));
                metroDb.AddInParameter(dbCommand, "@document", DbType.String, customer.DocumentNumber);
                metroDb.ExecuteNonQuery(dbCommand);

                if (customer.DocumentType.Equals("NIT"))
                {
                    sqlCommand = "UPDATE [BusinessPerson] SET [businessName] = @businessName WHERE id = @idCustomer";
                    dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                    metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                    metroDb.AddInParameter(dbCommand, "@businessName", DbType.String, customer.Name);
                    metroDb.ExecuteNonQuery(dbCommand);
                }
                else if (customer.DocumentType.Equals("CC"))
                {
                    sqlCommand = "UPDATE [NaturalPerson] SET [name] = @name ,[sureName] = @sureName WHERE  id = @idCustomer";
                    dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                    metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                    metroDb.AddInParameter(dbCommand, "@name", DbType.String, customer.Name);
                    metroDb.AddInParameter(dbCommand, "@sureName", DbType.String, customer.SureName);
                    metroDb.ExecuteNonQuery(dbCommand);
                }
            
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void CreateCustomer(Customer customer)
        {
            Guid idCustomer = Guid.NewGuid();
            try
            {
                string sqlCommand = "INSERT INTO [Customer] ([idCustomer],[idCity],[nemotechnic],[documentType],[state],[customerType],[document]"+
                                    ",[commission],[payTaxes],[transitBuyCredit],[transitSellCredit],[buyCredit],[sellCredit],[position],[profit],[avgBuy],[avgSell])" +
                                    "VALUES (@idCustomer,@idCity,@nemotechnic,@documentType,@state,@customerType,@document,@commission,1,0,0,0,0,0,0,0,0)";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.AddInParameter(dbCommand, "@idCity", DbType.Int32 , int.Parse(customer.CityId));
                metroDb.AddInParameter(dbCommand, "@nemotechnic", DbType.String, customer.TechnicalName);
                metroDb.AddInParameter(dbCommand, "@documentType", DbType.String, customer.DocumentType);
                metroDb.AddInParameter(dbCommand, "@state", DbType.String, CustomerServices.State2String(customer.State));
                metroDb.AddInParameter(dbCommand, "@customerType", DbType.String, CustomerServices.CustomerType2String(customer.CustomerType));
                metroDb.AddInParameter(dbCommand, "@document", DbType.String, customer.DocumentNumber);
                metroDb.AddInParameter(dbCommand, "@commission", DbType.Currency, customer.Commission);
                metroDb.ExecuteNonQuery(dbCommand);


                if (customer.DocumentType.Equals("NIT"))
                {
                    sqlCommand = "INSERT INTO [BusinessPerson] ([id],[businessName]) VALUES (@idCustomer,@businessName)";
                    dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                    metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                    metroDb.AddInParameter(dbCommand, "@businessName", DbType.String, customer.Name);
                    metroDb.ExecuteNonQuery(dbCommand);
                }
                else if (customer.DocumentType.Equals("CC"))
                {
                    sqlCommand = "INSERT INTO [NaturalPerson] ([id],[name],[sureName]) VALUES  (@idCustomer,@name,@sureName)";
                    dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                    metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                    metroDb.AddInParameter(dbCommand, "@name", DbType.String, customer.Name);
                    metroDb.AddInParameter(dbCommand, "@sureName", DbType.String, customer.SureName);
                    metroDb.ExecuteNonQuery(dbCommand);
                }


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void DeleteCustomer(Customer customer)
        {
            Guid idCustomer= new Guid(customer.Id);
            try
            {
                string sqlCommand = "UPDATE [Customer] SET [state] = 'I'  WHERE idCustomer = @idCustomer";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                 
                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.ExecuteNonQuery(dbCommand);

                sqlCommand = "UPDATE [User] SET [state] = 'I'  WHERE idCustomer = @idCustomer";
                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                metroDb.ExecuteNonQuery(dbCommand); 

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public Customer GetCustomer(string idCustomer)
        {
            Guid id = new Guid(idCustomer);
            Customer retCustomer = null;
            int idCity=0;
            string tempString = string.Empty; 
            decimal tempDecimal =0;
            City customerCity = null;

            try
            {
                string sqlCommand = "SELECT [name],[sureName],[idCity],[nemotechnic],"+
                                    "[documentType],[state],[customerType],[document],[commission],[payTaxes],"+
                                    "[transitBuyCredit],[transitSellCredit],[buyCredit],[sellCredit],[position],"+
                                    "[profit],[avgSell],[avgBuy] FROM [Customers] WHERE [idCustomer] = @idCustomer";
                DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, id);
               
                using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                {
                    while (dataReader.Read())
                    {
                        retCustomer = new Customer();
                        retCustomer.Id = idCustomer;
                        retCustomer.Name = dataReader["name"].ToString();
                        retCustomer.SureName = dataReader["sureName"].ToString();
                        idCity= Convert.ToInt32(dataReader["idCity"]);

                        customerCity  = this.GetCity(idCity);
                        retCustomer.CityId = customerCity.Id;
                        retCustomer.CityName  = customerCity.Name;
                        retCustomer.TechnicalName = dataReader["nemotechnic"].ToString();
                        tempString = dataReader["documentType"].ToString();
                        retCustomer.DocumentType = tempString;
                        tempString = dataReader["state"].ToString();
                        retCustomer.State = CustomerServices.GetState(tempString);
                        tempString = dataReader["customerType"].ToString();
                        retCustomer.CustomerType = CustomerServices.GetCustomerType(tempString);
                        retCustomer.DocumentNumber = dataReader["document"].ToString();
                        tempDecimal = Convert.ToDecimal(dataReader["commission"]);
                        retCustomer.Commission = tempDecimal;
                        retCustomer.PayTaxes = Convert.ToBoolean(dataReader["payTaxes"]); 
                        tempDecimal = Convert.ToDecimal(dataReader["transitBuyCredit"]);
                        retCustomer.TransitBuyCredit = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["transitSellCredit"]);
                        retCustomer.TransitSellCredit  = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["buyCredit"]);
                        retCustomer.BuyCredit = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["sellCredit"]);
                        retCustomer.SellCredit = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["position"]);
                        retCustomer.Position = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["profit"]);
                        retCustomer.Profit = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["avgSell"]);
                        retCustomer.AvgSell = tempDecimal;
                        tempDecimal = Convert.ToDecimal(dataReader["avgBuy"]);
                        retCustomer.AvgBuy = tempDecimal;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return retCustomer; 
        }
    }
}
